package com.supermap.wzhy.module.fr.service;

import com.supermap.wzhy.data.ExcelUtil;
import com.supermap.wzhy.module.fr.dao.FrDao;
import com.supermap.wzhy.module.mlk.dao.MlDao;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletRequest;
import java.util.ArrayList;
import java.util.List;

/**
 * 工商名录对账功能
 * Created by Sun'fei on 17-3-29.
 */
@Service
public class DataComparseService {

    @Autowired
    FrDao frDao;

    @Autowired
    MlDao mlDao;


    //工商汇总表
    private final String hz = "hz";
    private final String hzjg = "hzjg";

    //名录汇总表
    private final String ml_hz = "HZ";
    private final String ml_hzjg = "HZJG";

    /**
     * 合并工商14张表汇总视图
     * @return
     */
    public List getGSData(){
        //汇总结果信息
        String base_sql = "select * from hzjg order by aa";
        List list = frDao.query(base_sql);

        return  list;
    }


    /**
     * 合并名录14张表汇总视图
     * @return
     */
    public List getMLData(){
        //汇总结果信息
        String base_sql = "select * from hzjg order by aa";
        List list = mlDao.query(base_sql);

        return  list;
    }

    /**
     * 更新工商库汇总信息
     * @return
     */
    public boolean refalshGSTable(){
        //判断表是否存在
        String sql = "select count(*) from systables where tabname = ?";

        String hz_result = frDao.query(sql,hz).get(0).toString();
        if("0".equals(hz_result)){
            String create = createPoolInfo(hz);
            frDao.execute(create);
            System.out.println("工商-创建表 "+hz);
        }
        refalshGSPoolInfo();

        String hzjg_result = frDao.query(sql,hzjg).get(0).toString();
        if("0".equals(hzjg_result)){
            String create = createPoolInfo(hzjg);
            frDao.execute(create);
            System.out.println("工商-创建表 "+hzjg);
        }
        refalshGSPoolResult(hzjg);

        return true;
    }


    /**
     * 更新名录库汇总信息
     * @return
     */
    public boolean refalshMLTable(){
        //判断表是否存在
        String sql = "select count(*) from user_tables where table_name = ?";

        String hz_re = mlDao.query(sql,ml_hz).get(0).toString();
        if(!"0".equals(hz_re)){
            String drop = "drop table "+ml_hz;
            mlDao.execute(drop);
        }
        String create_hz = createPoolInfo(ml_hz);
        mlDao.execute(create_hz);
        System.out.println("名录-创建表 "+ml_hz);

        refalshMLPoolInfo();

        String hzjg_re = mlDao.query(sql,ml_hzjg).get(0).toString();
        if(!"0".equals(hzjg_re)){
            String drop = "drop table "+ml_hzjg;
            mlDao.execute(drop);
        }
        String create_hzjg = createPoolInfo(ml_hzjg);
        mlDao.execute(create_hzjg);
        System.out.println("名录-创建表 "+ml_hzjg);

        refalshMLPoolResult(ml_hzjg);
        return true;
    }


    /**
     * 刷新汇总信息
     * @return
     */
    private boolean refalshGSPoolInfo(){
        //清空汇总信息表
        String del_sql = "delete from "+hz;

        //汇总基本信息
        String a1_sql = "insert into hz(aa,a1) select s_ext_timestamp as aa,count(*) as a1 from yzym_e_baseinfo group by s_ext_timestamp";
        //"--汇总股东信息"
        String a2_sql = "insert into hz(aa,a2) select s_ext_timestamp as aa,count(*) as a2 from yzym_e_inv group by s_ext_timestamp";
        //"--汇总代表人信息" +
        String a3_sql  = "insert into hz(aa,a3) select s_ext_timestamp as aa,count(*) as a3 from yzym_e_lerep group by s_ext_timestamp";
        //"--汇总财务负责人信息" +
        String a4_sql  = "insert into hz(aa,a4) select s_ext_timestamp as aa,count(*) as a4 from yzym_e_finance group by s_ext_timestamp";
        //"--汇总变更信息" +
        String a5_sql  = "insert into hz(aa,a5) select s_ext_timestamp as aa,count(*) as a5 from yzym_e_alt group by s_ext_timestamp";
        //"--汇总注销信息" +
        String a6_sql  = "insert into hz(aa,a6) select s_ext_timestamp,count(*) from yzym_e_cancel group by s_ext_timestamp";
        //"--汇总隶属企业" +
        String a7_sql  = "insert into hz(aa,a7) select s_ext_timestamp,count(*) from yzym_e_sub group by s_ext_timestamp";
        //"--汇总年报信息" +
        String a8_sql  =  "insert into hz(aa,a8) select s_ext_timestamp,count(*) from yzym_an_baseinfo group by s_ext_timestamp";
        //"--汇总股东出资信息" +
        String a9_sql  =  "insert into hz(aa,a9) select s_ext_timestamp,count(*) from yzym_an_subcapital group by s_ext_timestamp";
        //"--汇总对外投资信息" +
        String a10_sql  = "insert into hz(aa,a10) select s_ext_timestamp,count(*) from yzym_an_forinvestment group by s_ext_timestamp";
        //"--汇总网站信息" +
        String a11_sql  = "insert into hz(aa,a11) select s_ext_timestamp,count(*) from yzym_an_websiteinfo group by s_ext_timestamp";
        //"--汇总股权变更信息" +
        String a12_sql  = "insert into hz(aa,a12) select s_ext_timestamp,count(*) from yzym_an_alterstockinfo group by s_ext_timestamp";
        //"--汇总经营异常名录" +
        String a13_sql  = "insert into hz(aa,a13) select s_ext_timestamp,count(*) from yzym_ao_opanomaly group by s_ext_timestamp";
        //"--汇总违法失信" +
        String a14_sql  = "insert into hz(aa,a14) select s_ext_timestamp,count(*) from yzym_e_li_illdishonesty group by s_ext_timestamp";


        try{
            frDao.execute(del_sql);
            //System.out.println("工商-清空汇总表 ");

            frDao.execute(a1_sql);
            frDao.execute(a2_sql);
            frDao.execute(a3_sql);
            frDao.execute(a4_sql);
            frDao.execute(a5_sql);
            frDao.execute(a6_sql);
            frDao.execute(a7_sql);
            frDao.execute(a8_sql);
            frDao.execute(a9_sql);
            frDao.execute(a10_sql);
            frDao.execute(a11_sql);
            frDao.execute(a12_sql);
            frDao.execute(a13_sql);
            frDao.execute(a14_sql);

            System.out.println("工商-更新汇总表");

        }catch (Exception e){
            System.out.println("工商刷新 "+hz+" 表失败！");
            return false;
        }

        return  true;
    }




    /**
     * 刷新汇总信息
     * @return
     */
    private boolean refalshMLPoolInfo(){
        //清空汇总信息表
        String del_sql = "delete from "+ml_hz;

        //汇总基本信息
        String a1_sql = "insert into hz(aa,a1) select 数据提取时间,count(*) from 工商登记信息 group by 数据提取时间";
        //"--汇总股东信息"
        String a2_sql = "insert into hz(aa,a2) select 数据提取时间,count(*) from 工商企业股东信息 group by 数据提取时间";
        //"--汇总代表人信息" +
        String a3_sql  = "insert into hz(aa,a3) select 数据提取时间,count(*) from 工商企业法定代表人信息 group by 数据提取时间";
        //"--汇总财务负责人信息" +
        String a4_sql  = "insert into hz(aa,a4) select 数据提取时间,count(*) from 工商企业财务负责人信息 group by 数据提取时间";
        //"--汇总变更信息" +
        String a5_sql  = "insert into hz(aa,a5) select 数据提取时间,count(*) from 工商变更登记信息 group by 数据提取时间";
        //"--汇总注销信息" +
        String a6_sql  = "insert into hz(aa,a6) select 数据提取时间,count(*) from 工商注销信息 group by 数据提取时间";
        //"--汇总隶属企业" +
        String a7_sql  = "insert into hz(aa,a7) select 数据提取时间,count(*) from 工商隶属企业信息 group by 数据提取时间";
        //"--汇总年报信息" +
        String a8_sql  =  "insert into hz(aa,a8) select 数据提取时间,count(*) from 工商企业年报信息 group by 数据提取时间";
        //"--汇总股东出资信息" +
        String a9_sql  =  "insert into hz(aa,a9) select 数据提取时间,count(*) from 工商企业年报股东出资信息 group by 数据提取时间";
        //"--汇总对外投资信息" +
        String a10_sql  = "insert into hz(aa,a10) select 数据提取时间,count(*) from 工商企业年报对外投资信息 group by 数据提取时间";
        //"--汇总网站信息" +
        String a11_sql  = "insert into hz(aa,a11) select 数据提取时间,count(*) from 工商企业年报网站网店信息 group by 数据提取时间";
        //"--汇总股权变更信息" +
        String a12_sql  = "insert into hz(aa,a12) select 数据提取时间,count(*) from 工商企业年报股权变更信息 group by 数据提取时间";
        //"--汇总经营异常名录" +
        String a13_sql  = "insert into hz(aa,a13) select 数据提取时间,count(*) from 工商经营异常名录 group by 数据提取时间";
        //"--汇总违法失信" +
        String a14_sql  = "insert into hz(aa,a14) select 数据提取时间,count(*) from 工商违法失信企业名单 group by 数据提取时间";


        try{
            mlDao.execute(del_sql);
            //System.out.println("名录清空汇总表 ");

            mlDao.execute(a1_sql);
            mlDao.execute(a2_sql);
            mlDao.execute(a3_sql);
            mlDao.execute(a4_sql);
            mlDao.execute(a5_sql);
            mlDao.execute(a6_sql);
            mlDao.execute(a7_sql);
            mlDao.execute(a8_sql);
            mlDao.execute(a9_sql);
            mlDao.execute(a10_sql);
            mlDao.execute(a11_sql);
            mlDao.execute(a12_sql);
            mlDao.execute(a13_sql);
            mlDao.execute(a14_sql);

            System.out.println("名录-更新汇总表");

        }catch (Exception e){
            System.out.println("名录刷新 "+hz+" 表失败！");
            return false;
        }

        return  true;
    }


    /**
     * 刷新汇总结果
     * @return
     */
    private boolean refalshMLPoolResult(String tableName){

        String del_sql = "delete from "+tableName;

        String insert_sql = "insert into "+tableName+" select to_date(to_char(aa, 'yyyy-mm-dd'),'yyyy-mm-dd'),sum(a1) as a1,sum(a2) as a2,sum(a3) as a3," +
                "sum(a4) as a4,sum(a5) as a5,sum(a6) as a6,sum(a7) as a7,sum(a8) as a8,sum(a9) as a9," +
                "sum(a10) as a10,sum(a11) as a11,sum(a12) as a12,sum(a13) as a13,sum(a14) as a14 " +
                "from hz group by to_date(to_char(aa, 'yyyy-mm-dd'),'yyyy-mm-dd')";

        try{
            mlDao.execute(del_sql);
            mlDao.execute(insert_sql);
            System.out.println("名录-更新汇总结果表");
        }catch (Exception e){
            System.out.println("刷新 "+tableName+" 表失败！");
            return false;
        }
        return true;
    }

    /**
     * 刷新汇总结果
     * @return
     */
    private boolean refalshGSPoolResult(String tableName){

        String del_sql = "delete from "+tableName;

        String insert_sql = "insert into "+tableName+" select aa,sum(a1) as a1,sum(a2) as a2,sum(a3) as a3," +
                "sum(a4) as a4,sum(a5) as a5,sum(a6) as a6,sum(a7) as a7,sum(a8) as a8,sum(a9) as a9," +
                "sum(a10) as a10,sum(a11) as a11,sum(a12) as a12,sum(a13) as a13,sum(a14) as a14 " +
                "from hz group by aa";

        try{
            frDao.execute(del_sql);
            frDao.execute(insert_sql);
            System.out.println("工商-更新汇总结果表");
        }catch (Exception e){
            System.out.println("刷新 "+tableName+" 表失败！");
            return false;
        }
        return true;
    }


    /**
     * 创建汇总信息表
     * @return
     */
    private String createPoolInfo(String tableName){

        String sql = "create table "+tableName+"(" +
                "aa date," +
                "a1 number," +
                "a2 number," +
                "a3 number," +
                "a4 number," +
                "a5 number," +
                "a6 number," +
                "a7 number," +
                "a8 number," +
                "a9 number," +
                "a10 number," +
                "a11 number," +
                "a12 number," +
                "a13 number," +
                "a14 number" +
                ")";
        return  sql;
    }


    /**
     * 下载汇总结果
     */
    public boolean downloadExcel(HttpServletRequest request,String title,String excel_head,String etp){
        boolean boo = false;
        String head[] = excel_head.split(",");
        List<Object [] > list = new ArrayList<>();
        if("gs".equals(etp)){
            list = getGSData();
        }
        if("ml".equals(etp)){
            list = getMLData();
        }

        //汇总数据转换二维数组
        String [][] data = new String[list.size()][head.length];
        for(int i = 0;i < list.size();i++){
            Object [] _data = list.get(i);
            for(int j = 0;j < _data.length;j++){
                if(_data[j] != null)
                    data[i][j] = _data[j].toString();
                else
                    data[i][j] = "0";
            }
        }
        try {

            HSSFWorkbook hw = new HSSFWorkbook();
            if("gs".equals(etp)){
                hw = ExcelUtil.dataToWorkbook("工商汇总结果", head, data);
            }
            if("ml".equals(etp)){
                hw = ExcelUtil.dataToWorkbook("名录汇总结果", head, data);
            }

            request.getSession().setAttribute("fileName",title);
            request.getSession().setAttribute("wk",hw);
            boo = true;
        }catch (Exception e){
            System.out.println("下载结果异常！");
            e.printStackTrace();
        }finally {
            return boo;
        }
    }


}
